# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import sqlite3
import time
import hashlib
from scrapy.http import Request
from scrapy.pipelines.images import ImagesPipeline


class ImagePipeline(ImagesPipeline):
    def item_completed(self, results, item, info):
        print("这是results", results)
        item['img_path'] = results[0][1]['path']
        item['img_url'] = item['img_url'][0]
        return item

    def file_path(self, request, response=None, info=None):
        # nav_title/big_cate_title/small_cate_title/img_title.jpg
        meta = request.meta
        img_nav_cate = meta['img_nav_cate']
        img_big_cate = meta['img_big_cate']
        img_small_cate = meta['img_small_cate']

        md = hashlib.md5()
        md.update(meta['img_url'][0].encode())
        result = md.hexdigest()

        img_title = meta['img_title'] + result

        return f'{img_nav_cate}/{img_big_cate}/{img_small_cate}/{img_title}.jpg'

    def get_media_requests(self, item, info):
        return [Request(x, meta=item) for x in item.get(self.images_urls_field, [])]


class SqlitePipeline(object):

    def __init__(self):
        self.con = sqlite3.connect('ivsky.sqlite')
        self.cursor = self.con.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.con.close()
        self.cursor = None
        self.con = None

    def process_item(self, item, spider):
        # 建表语句
        last_sql = ''
        all_keys = []
        all_values = []
        for per_key, per_value in item.items():
            if isinstance(per_value, int):
                last_sql += f'{per_key} INTEGER,'
            elif isinstance(per_value, str):
                last_sql += f'{per_key} VARCHAR(255),'
            elif isinstance(per_value, float):
                last_sql += f'{per_key} FLOAT,'
            elif isinstance(per_key, list):
                # list型的需不需要？？
                last_sql += f'{per_key} VARCHAR(255),'

            all_keys.append(per_key)
            all_values.append(per_value)

        # 组合create/insert语句
        last_sql = last_sql[:-1]
        all_keys = ','.join(all_keys)

        sql = f"""
            CREATE TABLE IF NOT EXISTS {spider.name}(
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                {last_sql}
            );
        """
        print(sql)
        self.cursor.execute(sql)

        sql2 = f"""
            INSERT INTO {spider.name}({all_keys}) VALUES ({('?,'*len(all_values))[:-1]});
        """
        print(sql2)
        self.cursor.execute(sql2, all_values)
        self.con.commit()
        return item
